package com.kaizeli.website.service;

import com.kaizeli.website.response.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.Map;

/**
 * 数据库初始化服务
 * 用于自动创建验收审核相关的数据库表
 */
@Service
public class DatabaseInitService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 初始化验收审核相关表
     */
    @Transactional
    public Result<Void> initAcceptanceTables() {
        try {
            // 读取SQL脚本
            String sqlScript = readSqlScript("sql/init_acceptance_tables.sql");
            
            // 分割SQL语句并执行
            String[] statements = sqlScript.split(";");
            for (String statement : statements) {
                statement = statement.trim();
                if (!statement.isEmpty() && !statement.startsWith("--")) {
                    jdbcTemplate.execute(statement);
                }
            }
            
            return Result.success();
        } catch (Exception e) {
            return Result.error(500, "初始化数据库表失败：" + e.getMessage());
        }
    }

    /**
     * 检查表是否存在
     */
    public Result<Boolean> checkTableExists(String tableName) {
        try {
            String sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName);
            return Result.success(count > 0);
        } catch (Exception e) {
            return Result.error(500, "检查表是否存在失败：" + e.getMessage());
        }
    }

    /**
     * 获取表记录数
     * 注意：为防止SQL注入，tableName应该通过白名单验证
     */
    public Result<Integer> getTableRecordCount(String tableName) {
        try {
            // 白名单验证：只允许查询特定的表
            if (!isValidTableName(tableName)) {
                return Result.error(400, "无效的表名");
            }
            
            // 使用参数化查询获取表记录数
            // 注意：表名无法使用参数化，必须通过白名单验证
            String sql = "SELECT COUNT(*) FROM " + tableName;
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            return Result.success(count);
        } catch (Exception e) {
            return Result.error(500, "获取表记录数失败：" + e.getMessage());
        }
    }
    
    /**
     * 白名单验证：检查表名是否合法
     * 只允许查询预定义的表
     */
    private boolean isValidTableName(String tableName) {
        // 定义允许查询的表白名单
        java.util.Set<String> validTables = java.util.Set.of(
            "project",
            "employee",
            "acceptance_node",
            "employee_acceptance",
            "travel_expense",
            "payment",
            "warning_records",
            "warning_rules",
            "employee_active",
            "employee_resigned",
            "contract_personnel"
        );
        
        return validTables.contains(tableName);
    }

    /**
     * 获取数据库信息
     */
    public Result<Map<String, Object>> getDatabaseInfo() {
        try {
            String sql = "SELECT DATABASE() as database_name, VERSION() as version, NOW() as current_time";
            Map<String, Object> info = jdbcTemplate.queryForMap(sql);
            return Result.success(info);
        } catch (Exception e) {
            return Result.error(500, "获取数据库信息失败：" + e.getMessage());
        }
    }

    /**
     * 读取SQL脚本文件
     */
    private String readSqlScript(String scriptPath) throws IOException {
        ClassPathResource resource = new ClassPathResource(scriptPath);
        StringBuilder content = new StringBuilder();
        
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(resource.getInputStream(), StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                content.append(line).append("\n");
            }
        }
        
        return content.toString();
    }
}
